package com.test.hanlp.participle.service.impl;

import com.test.hanlp.config.FileConfig;
import com.test.hanlp.participle.po.CustomTermPo;
import com.test.hanlp.participle.repository.ICustomTermRepository;
import com.test.hanlp.participle.service.IWordVerifiedService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @author : ky.zhang
 * @description :校验服务业务类
 * @createDate : 2024/8/12 10:46
 */
@Service
@Slf4j
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class WordVerifiedService implements IWordVerifiedService {

    Map<String, String> causeMap = new HashMap<>();
    private final FileConfig fileConfig;
    private final ICustomTermRepository customTermRepository;
    private final WordSegmentationService wordSegmentationService;
    //校验失败的字段
    List<String> failColumnList = new ArrayList<>();
    //校验成功的字段
    List<String> successColumnList = new ArrayList<>();

    @Override
    public void verified(String tablePath) {
        try {
            if (tablePath == null) {
                throw new RuntimeException("Excel文件不存在");
            }

            File tableFile = new File(tablePath);
            if (!tableFile.exists()) {
                throw new RuntimeException("指定的Excel文件不存在: " + tablePath);
            }

            // 使用try-with-resources自动管理资源
            try (FileInputStream fileInputStream1 = new FileInputStream(tableFile);
                 Workbook workbook1 = WorkbookFactory.create(fileInputStream1)) {

                // 创建词根文件对象列表并加载工作簿
                List<File> fileList = getFileList(fileConfig.getWordPaths());
                List<Workbook> workbookList;
                try (Stream<File> fileStream = fileList.stream();
                     Stream<Workbook> workbookStream = fileStream.map(file -> {
                         try (FileInputStream fileInputStream = new FileInputStream(file)) {
                             return WorkbookFactory.create(fileInputStream);
                         } catch (IOException e) {
                             throw new RuntimeException("无法加载Excel文件: " + file.getPath(), e);
                         }
                     })) {
                    workbookList = workbookStream.collect(Collectors.toList());
                }

                // 词根表存入数据库
                readWordData(workbookList);

                Sheet tableSheet = workbook1.getSheetAt(0);
                int numberOfSheets = workbook1.getNumberOfSheets();

                if (numberOfSheets == 3) {
                    Sheet tableConfigSheet = workbook1.getSheetAt(1);
                    Sheet databaseSheet = workbook1.getSheetAt(2);

                    // 读取数据库配置单元
                    Map<String, String> dataBaseConfig = readDataBaseConfig(databaseSheet);
                    // 读取表配置单元
                    Map<String, String> tableConfig = readTableConfig(tableConfigSheet, dataBaseConfig);

                    // 读取表结构数据<字段名,注释>
                    Map<String, String> tableMap = readStructureData(tableSheet, tableConfig);
                    if (!tableMap.isEmpty()){
                        // 字段注释进行分词
                        Map<Map<String, String>, List<List<CustomTermPo>>> participleMap = participle(tableMap);

                        // 字段校验
                        compareData(participleMap, workbook1, tableFile, tableMap);

                        // 修改表校验状态
                        updateTableConfig(tableFile, workbook1, tableConfigSheet, tableConfig);
                    }else {
                        log.info("未读取表结构，请查看表配置是否选择表");
                    }
                } else {
                    // 读取表结构数据<字段名,注释>
                    Map<String, String> tableMap = readStructureData1(tableSheet);
                    // 字段注释进行分词
                    Map<Map<String, String>, List<List<CustomTermPo>>> participleMap = participle(tableMap);
                    // 字段校验
                    compareData(participleMap, workbook1, tableFile, tableMap);
                }
            } catch (IOException e) {
                throw new RuntimeException("Excel文件处理失败", e);
            }
        } catch (Exception e) {
            throw new RuntimeException("校验执行失败", e);
        }
    }

    private Map<String, String> readStructureData1(Sheet sheet) {
        Map<String, String> tableMap;
        //读取字段名和注释
        try {
            tableMap = getTableMap(sheet);
        }catch (Exception e){
            throw new RuntimeException("字段名和注释读取失败");
        }
        return tableMap;
    }

    private Map<String, String> getTableMap(Sheet sheet) {
        Map<String, String> resultMap = new HashMap<>();
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { // 从1开始，跳过标题行
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                String columnName = row.getCell(1).toString();
                String comment = row.getCell(2).toString();
                resultMap.put(columnName, comment);
            }
        }
        return resultMap;
    }

    /**
     * 创建词根文件对象
     */
    private List<File> getFileList(List<String> wordPaths) {
        List<File> fileList = new ArrayList<>();
        for (String wordPath : wordPaths) {
            File file = new File(wordPath);
            if (!file.exists()) {
                throw new RuntimeException("词根文件不存在");
            }
            fileList.add(file);
        }
        return fileList;
    }


    private synchronized void updateTableConfig(File file, Workbook workbook, Sheet tableConfigSheet, Map<String, String> tableConfig) {
        for (int i = tableConfigSheet.getFirstRowNum() + 1; i <= tableConfigSheet.getLastRowNum(); i++) {
            Row row = tableConfigSheet.getRow(i);
            if (row != null) {
                String tableName = row.getCell(1).toString();
                if (tableConfig.containsKey(tableName) && tableConfig.get(tableName).equals("y")) {
                    row.getCell(2).setCellValue("n");
                }
            }
        }

        // 将更改写回文件
        try (FileOutputStream outputStream = new FileOutputStream(file)) {
            log.info("正在修改表校验状态...");
            workbook.write(outputStream);
        } catch (Exception e) {
            throw new RuntimeException("写入文件失败");
        } finally {
            log.info("表校验状态修改成功");
        }
    }

    /**
     * 读取表配置
     */
    private Map<String, String> readTableConfig(Sheet tableConfigSheet, Map<String, String> dataBaseConfig) {
        List<Integer> lastRowList = getLastRowList(tableConfigSheet);
        return getFilterMap(tableConfigSheet, dataBaseConfig, lastRowList);
    }

    /**
     * 读取数据库配置单元
     */
    private Map<String, String> readDataBaseConfig(Sheet databaseSheet) {
        Map<String, String> databaseMap = new HashMap<>();
        for (int i = databaseSheet.getFirstRowNum() + 1; i <= databaseSheet.getLastRowNum(); i++) {
            Row row = databaseSheet.getRow(i);
            if (row != null) {
                Cell dbNameCell = row.getCell(0);
                Cell selectCell = row.getCell(1);

                if (dbNameCell != null && selectCell != null) {
                    String dbName = dbNameCell.getStringCellValue();
                    String select = selectCell.getStringCellValue();
                    databaseMap.put(dbName, select);
                }
            }
        }
        return databaseMap;
    }

    /**
     * 字段注释进行分词
     * 若注释分词后在词根表不存在则分词后表结构中的comment为Null,分此后的词根对象为Null
     *
     * @return map<分词后表结构, 分词后的词根对象>
     */
    private Map<Map<String, String>, List<List<CustomTermPo>>> participle(Map<String, String> tableMap) {
        Map<Map<String, String>, List<List<CustomTermPo>>> resultMap = new HashMap<>();
        try {
            //分词后表结构，不包含词根的注释设置为null
            Map<String, String> participleTableMap = new HashMap<>();
            //注释分词后词根对象
            List<List<CustomTermPo>> commentParticipleList = new ArrayList<>();
            for (Map.Entry<String, String> entry : tableMap.entrySet()) {
                List<CustomTermPo> resultList = new ArrayList<>();
                String comment = entry.getValue();
                List<CustomTermPo> customTermPoList = wordSegmentationService.segment1(comment);
                //遍历判断注释分词后在词根表是否存在
                for (CustomTermPo customTermPo : customTermPoList) {
                    if (customTermPo.getReplacement() == null && customTermPo.getAbbreviation() == null) {
                        continue;
                    }
                    resultList.add(customTermPo);
                }
                if (resultList.isEmpty()) {
                    failColumnList.add(entry.getKey());
                    causeMap.put(comment, "字段不符，原因：注释不包含词根");
                    continue;
                }
                participleTableMap.put(entry.getKey(), entry.getValue());
                commentParticipleList.add(resultList);
            }
            resultMap.put(participleTableMap, commentParticipleList);
        }catch (Exception e){
            throw new RuntimeException("字段注释分词失败");
        }

        return resultMap;
    }

    /**
     * 字段校验
     *
     * @param participleMap Map<注释分词后表结构,注释分词后在词根表中的对应对象>
     */
    private synchronized void compareData(Map<Map<String, String>, List<List<CustomTermPo>>> participleMap, Workbook workbook, File file, Map<String, String> tableMap) {
        for (Map.Entry<Map<String, String>, List<List<CustomTermPo>>> entry : participleMap.entrySet()) {
            //分词后表结构
            Map<String, String> participTableMap = entry.getKey();
            Set<String> keySet = participTableMap.keySet();
            if (keySet.isEmpty()){
                break;
            }
            //分词后注释相关词根对象
            List<List<CustomTermPo>> participleCommentList = entry.getValue();
            //校验字段名是否符合格式
            for (String columnName : keySet) {
                //注释级
                for (List<CustomTermPo> customTermPoList : participleCommentList) {
                    //词根级
                    for (int i = 0; i < customTermPoList.size(); i++) {
                        CustomTermPo customTermPo = customTermPoList.get(i);
                        if (!participTableMap.get(columnName).equals(customTermPo.getComment())) {
                            continue;
                        }
                        String replacement = customTermPo.getReplacement();
                        String abbreviation = customTermPo.getAbbreviation();
                        if (!columnName.contains(replacement) && !columnName.contains(abbreviation)) {
                            causeMap.put(customTermPo.getComment(), "字段不符，原因：字段不符合格式，应包含:" + replacement + "或：" + abbreviation);
                            failColumnList.add(columnName);
                            break;
                        }
                        if (i == customTermPoList.size() - 1) {
                            successColumnList.add(columnName);
                        }
                    }
                }
            }
        }

        //将校验结果插入excel中
        if (!tableMap.isEmpty()){
            Sheet sheet = workbook.getSheetAt(0);
            for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {
                Row row = sheet.getRow(j);
                if (row != null) {
                    String columnName = row.getCell(1).toString();
                    String comment = row.getCell(2).toString();
                    if (tableMap.containsValue(comment)) {
                        Cell cell = row.getCell(3);
                        if (cell == null) {
                            cell = row.createCell(3);
                        }
                        if (failColumnList.contains(columnName) && causeMap.containsKey(comment)) {
                            cell.setCellValue(causeMap.get(comment));
                        }
                        if (successColumnList.contains(columnName)&&!causeMap.containsKey(comment)) {
                            cell.setCellValue("字段符合");
                        }
                    }
                }
            }

            try (FileOutputStream outputStream = new FileOutputStream(file)) {
                log.info("校验数据正在写入文件");
                workbook.write(outputStream);
            } catch (Exception e) {
                throw new RuntimeException("写入文件失败");
            } finally {
                log.info("数据写入成功");
            }
        }
        log.info("校验完成");
        log.info("校验不符的字段：{}", failColumnList);
        log.info("校验符合的字段：{}", successColumnList);
    }

    /**
     * 读取表结构excel
     */
    private Map<String, String> readStructureData(Sheet sheet, Map<String, String> tableConfig) {
        //获取排序好地合并行
        List<Integer> lastRowList = getLastRowList(sheet);
        //过滤未配置的
        return getFilterMap(sheet, tableConfig, lastRowList);
    }

    private Map<String, String> getFilterMap(Sheet sheet, Map<String, String> tableConfig, List<Integer> lastRowList) {
        //获取表结构map<字段名,注释>
        Map<String, String> resultMap = getTableMap(sheet);
        List<String> tableNameList = new ArrayList<>();

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                String tableName = row.getCell(0).toString();
                if (!tableName.isEmpty()) tableNameList.add(tableName);
            }
        }
        for (int i = 0; i < lastRowList.size(); i++) {
            if (tableConfig.get(tableNameList.get(i)).equals("n")) {
                if (i == 0) {
                    for (int j = 1; j <= lastRowList.get(i); j++) {
                        Row row1 = sheet.getRow(j);
                        resultMap.remove(row1.getCell(1).toString());
                    }
                } else {
                    for (int j = lastRowList.get(i - 1) + 1; j <= lastRowList.get(i); j++) {
                        Row row1 = sheet.getRow(j);
                        resultMap.remove(row1.getCell(1).toString());
                    }
                }
            }
        }
        return resultMap;
    }

    private List<Integer> getLastRowList(Sheet sheet) {
        List<Integer> lastRowList = new ArrayList<>();
        boolean skipFirstRow = true;
        for (Row row : sheet) {
            if (skipFirstRow) {
                skipFirstRow = false;
                continue;
            }
            Cell cell = row.getCell(0);
            int lastRowNum = findMergedRegionLastRow(sheet, row.getRowNum(), cell.getColumnIndex());
            if (!lastRowList.contains(lastRowNum)){
                lastRowList.add(lastRowNum);
            }
        }
        return lastRowList;
    }

    private int findMergedRegionLastRow(Sheet sheet, int rowNum, int columnIndex) {
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.isInRange(rowNum, columnIndex)) {
                return merged.getLastRow();
            }
        }
        return rowNum;
    }

    private void readWordData(List<Workbook> workbookList) {
        for (Workbook workbook : workbookList) {
            Sheet sheet = workbook.getSheetAt(0);
            //读取词根表并存入自定义术语表
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { // 从1开始，跳过标题行
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    String word = row.getCell(0).toString();
                    String replacement = row.getCell(1).toString();
                    String abbreviation = row.getCell(2).toString();
                    //若词根存在先删除原有数据再添加
                    if (customTermRepository.findByWordAndDeletedFalse(word).isPresent()) {
                        wordSegmentationService.removeTerm(word);
                    }
                    wordSegmentationService.addHanlpCustomDictionary(word);
                    customTermRepository.save(new CustomTermPo().setWord(word).setReplacement(replacement).setAbbreviation(abbreviation));
                }
            }
        }
    }

}
